<?php


namespace app\common\controller;

use PDOException;
use think\Db;
use think\Env;
use think\exception\ValidateException;
use think\Log;

/**
 * 
 * SQL 操作
 * Class Sqls
 * @package app\common\controller
 * 
 * @method mixed addColumn( string $column, string $comment = '未命名' )		 新增字段
 * @method mixed dropColumn( string $column )									删除字段
 * @method mixed checkColums( string $column ) 									查看字段是否存在
 */
class Sqls
{
	/**
	 * 实例对象
	 * @var object
	 */
	protected static $instance;

	/**
	 * 数据库
	 * @var string
	 */
	protected $database = null;

	/**
	 * 表前缀
	 * @var string
	 */
	protected $prefix = null;

	/**
	 * 表默认配置
	 * @var string
	 */
	protected $table = null;

	/**
	 * 字段类型
	 * @var string
	 */
	protected $typs = null;

	/**
	 * 字段长度
	 * @var string
	 */
	protected $len = 0;

	/**
	 * 字段默认值
	 * @var string
	 */
	protected $default = 'NULL';

	/**
	 * 引擎
	 * @var string
	 */
	protected $engine = "InnoDB";

	/**
	 * 设置字段
	 * @var array|null
	 */
	public $setFieldTpys = [
		"title" 			=> "varchar(50)",
		"act_id" 			=> "int(11)",
		"user_id" 			=> "int(11)",
		"order_id"			=> "int(11)",
		"admin_id"			=> "int(11)",
		"status"			=> "tinyint(1)",
		"process_status" 	=> "tinyint(1)"
	];

	/**
	 * 设置字段默认值
	 * @var array
	 */
	public $setFieldDefaultVals = [
		"title" 			=> 'null',
		"act_id" 			=> "0",
		"user_id" 			=> "0",
		"order_id"			=> "0",
		"admin_id"			=> "0",
		"status"			=> "1",
		"process_status" 	=> "2"
	];

	/**
	 * 字段注释
	 * @var array|null
	 */
	public $setFieldComment = [
		"title" 			=> "标题",
		"act_id" 			=> "活动ID",
		"user_id" 			=> "用户ID",
		"order_id"			=> "订单ID",
		"admin_id"			=> "管理ID",
		"status"			=> "状态:0=默认,1=正常,2=作废",
		"process_status" 	=> "处理状态:0=默认,1=已处理,2=未处理"
	];

	/**
	 * 默认架构方法
	 */
	public function __construct($key, $typs = '', $len = 0, $default = null)
	{
		$this->database = Env::get('database.database');
		$this->typs = $typs;
		$this->len  = $len;

		$constant = \app\common\constants\TabConstants::TABLES;
		$this->prefix = $constant["prefix"];
		if (!isset($constant[$key])) {
			$this->table = $key;
		} else {
			$this->table = $constant[$key];
		}
		if (!is_null($default) || $default != 'null') {
			$this->default = $default;
		}
	}

	/**
	 * 实例化
	 * @var object
	 */
	public static function instance($key, $typs = '', $len = 0, $default = null)
	{
		if (is_null(self::$instance)) {
			self::$instance = new static($key, $typs, $len, $default);
		}
		return self::$instance;
	}

	/**
	 * 查看表是否存在
	 *
	 * @param [type] $table
	 * @return boolean
	 */
	public function checkTable()
	{
		return $this->_query("SHOW TABLES like '{$this->prefix}{$this->table}'");
	}

	/**
	 * 创建数据表
	 *
	 * @param string $arr   ["title", "act_id", "user_id", "order_id", "admin_id", "status", "process_status"] 表字段
	 * @param string $comment		
	 * @return string
	 */
	public function createTable($arr,   $comment = "数据表")
	{
		$table = "";
		try {
			if (!empty($arr)) {
				# 创建验证
				$isExist = $this->checkTable();
				if (!$isExist) {
					$sql = $this->combCreateSqls($arr);
					$sql_str = "CREATE TABLE `{$this->prefix}{$this->table}` (\n ";
					$sql_str .= "`id` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增ID',";
					$sql_str .= $sql;
					$sql_str .=	"\n  PRIMARY KEY ( `id` ) USING BTREE  ";
					$sql_str .= "\n) ENGINE={$this->engine} DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='" . $comment . "';";
					$this->_execute($sql_str);
				}
				# 是否创建成功
				$isExist = $this->checkTable();
				if ($isExist) {
					$table = $this->table;
				}
			}
		} catch (ValidateException | PDOException | \Exception $e) {
			echo $e->getMessage();
		} finally {
			return $table;
		}
	}

	/**
	 * 拼组创建字段
	 */
	public function combCreateSqls($arr)
	{
		$str = "";
		foreach ($arr as $v) {
			$str .= "\n `{$v}` " . $this->setFieldTpys[$v] . " DEFAULT " . $this->setFieldDefaultVals[$v] . " COMMENT '" . $this->setFieldComment[$v] . "',";
		}
		return $str;
	}

	/**
	 * 新增字段
	 */
	public function addColumn($column, $comment = '未命名')
	{
		try {
			$check = $this->checkColums($column);
			if ($check) {
				$sql = "ALTER TABLE `{$this->database}`.`{$this->prefix}{$this->table}` ADD COLUMN `{$column}` {$this->typs}({$this->len}) DEFAULT {$this->default} COMMENT '{$comment}'";
				$this->_execute($sql);
			}
		} catch (\Exception $e) {
			Log::write($e->getMessage());
		}
	}

	/**
	 * 删除字段
	 */
	public function dropColumn($column)
	{
		try {
			$check = $this->checkColums($column);
			if ($check) {
				$sql = "ALTER TABLE `{$this->database}`.`{$this->prefix}{$this->table}` DROP COLUMN {$column}";
				$this->_execute($sql);
			}
		} catch (\Exception $e) {
			Log::write($e->getMessage());
		}
	}

	/**
	 * 查看字段是否存在
	 */
	public function checkColums($column)
	{
		if (!$column) {
			return false;
		}
		$sql = "SELECT count(*) con FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$this->database}' AND TABLE_NAME='{$this->prefix}{$this->table}' AND COLUMN_NAME = '{$column}'";
		$result = Db::query($sql);
		if (!$result[0]['con']) {
			return true;
		}
		return false;
	}

	/**
	 * 查询
	 */
	public function _query($sql)
	{
		return Db::query($sql);
	}

	/**
	 * 执行
	 */
	public function  _execute($sql)
	{
		Db::execute($sql);
	}
}
